package org.binge.widget.util;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.LinkedList;
import java.util.List;

public class ReadExcelUtil {
    private static DecimalFormat df = new DecimalFormat("0");// 格式化 number String
    private static SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 格式化日期字符串
    private static DecimalFormat nf = new DecimalFormat("0.00");// 格式化数字

    /**
     * 对外提供读取excel 的方法
     */
    public static List<List<Object>> readExcel(String remotePathAndName, int currentCount, int parseCount) throws IOException {
        String type = remotePathAndName.lastIndexOf(".") == -1 ? "" : remotePathAndName.substring(remotePathAndName.lastIndexOf(".") + 1);
        File file = new File(remotePathAndName);
        return readExcel(file, type, currentCount, parseCount);
    }

    /**
     * 读取 office 2003 excel
     *
     * @throws IOException
     * @throws FileNotFoundException
     */
    private static List<List<Object>> readExcel(File file, String type, int currentCount, int parseCount)
            throws IOException {
        List<List<Object>> list = new LinkedList<List<Object>>();
        Workbook workbook = null;
        if ("xls".equalsIgnoreCase(type)) {
            workbook = new HSSFWorkbook(new FileInputStream(file));
        } else if ("xlsx".equalsIgnoreCase(type)) {
            workbook = new XSSFWorkbook(new FileInputStream(file));
        } else {
            throw new IOException("不支持的文件类型");
        }
        Sheet sheet = workbook.getSheetAt(0);
        Row row = null;
        Cell cell = null;
        Object value = null;

        //解析方法及计数
        int counter = 0;//解析的非空行数据计数
        for (int i = currentCount + sheet.getFirstRowNum(); counter < parseCount && (currentCount + counter) < sheet.getPhysicalNumberOfRows(); i++) {
            row = sheet.getRow(i);
            if (row == null) {
                continue;
            } else {
                counter++;
            }
            List<Object> linked = new LinkedList<Object>();
            for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {
                cell = row.getCell(j);
                if (cell != null) {
                    switch (cell.getCellType()) {
                        case XSSFCell.CELL_TYPE_STRING:
//                        System.out.println(i + "行" + j + " 列 is String type");
                            value = cell.getStringCellValue();
                            break;
                        case XSSFCell.CELL_TYPE_NUMERIC:
//                        System.out.println(i + "行" + j
//                                + " 列 is Number type ; DateFormt:"
//                                + cell.getCellStyle().getDataFormatString());
                            if ("@".equals(cell.getCellStyle().getDataFormatString())) {
                                value = df.format(cell.getNumericCellValue());
                            } else if ("General".equals(cell.getCellStyle().getDataFormatString())) {
                                value = nf.format(cell.getNumericCellValue());
                            } else {
                                value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
                            }
                            break;
                        case XSSFCell.CELL_TYPE_BOOLEAN:
//                        System.out.println(i + "行" + j + " 列 is Boolean type");
                            value = cell.getBooleanCellValue();
                            break;
                        case XSSFCell.CELL_TYPE_BLANK:
//                        System.out.println(i + "行" + j + " 列 is Blank type");
                            value = "";
                            break;
                        default:
//                        System.out.println(i + "行" + j + " 列 is default type");
                            value = cell.toString();
                    }
                }else {
                    value = "";//add by zhangbin 2016-06-07
                }
                linked.add(value);
            }
            list.add(linked);
        }
        return list;
    }

    /**
     * 读取Office 2007 excel
     */
//    private static List<List<Object>> read2007Excel(File file)
//            throws IOException {
//        List<List<Object>> list = new LinkedList<List<Object>>();
//        // 构造 XSSFWorkbook 对象，strPath 传入文件路径
//        XSSFWorkbook xwb = new XSSFWorkbook(new FileInputStream(file));
//        // 读取第一章表格内容
//        XSSFSheet sheet = xwb.getSheetAt(0);
//        Object value = null;
//        XSSFRow row = null;
//        XSSFCell cell = null;
//        int counter = 0;
//        for (int i = sheet.getFirstRowNum(); counter < sheet.getPhysicalNumberOfRows(); i++) {
//            row = sheet.getRow(i);
//            if (row == null) {
//                continue;
//            } else {
//                counter++;
//            }
//            List<Object> linked = new LinkedList<Object>();
//            for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
//                cell = row.getCell(j);
//                if (cell == null) {
//                    continue;
//                }
//                switch (cell.getCellType()) {
//                    case XSSFCell.CELL_TYPE_STRING:
////                        System.out.println(i + "行" + j + " 列 is String type");
//                        value = cell.getStringCellValue();
//                        break;
//                    case XSSFCell.CELL_TYPE_NUMERIC:
////                        System.out.println(i + "行" + j
////                                + " 列 is Number type ; DateFormt:"
////                                + cell.getCellStyle().getDataFormatString());
//                        if ("@".equals(cell.getCellStyle().getDataFormatString())) {
//                            value = df.format(cell.getNumericCellValue());
//                        } else if ("General".equals(cell.getCellStyle().getDataFormatString())) {
//                            value = nf.format(cell.getNumericCellValue());
//                        } else {
//                            value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
//                        }
//                        break;
//                    case XSSFCell.CELL_TYPE_BOOLEAN:
////                        System.out.println(i + "行" + j + " 列 is Boolean type");
//                        value = cell.getBooleanCellValue();
//                        break;
//                    case XSSFCell.CELL_TYPE_BLANK:
////                        System.out.println(i + "行" + j + " 列 is Blank type");
//                        value = "";
//                        break;
//                    default:
////                        System.out.println(i + "行" + j + " 列 is default type");
//                        value = cell.toString();
//                }
//                if (value == null || "".equals(value)) {
//                    continue;
//                }
//                linked.add(value);
//            }
//            list.add(linked);
//        }
//        return list;
//    }

}